package com.application.chiettinhdienluc.db;

import java.util.ArrayList;

import android.content.ContentValues;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;

import com.application.chiettinhdienluc.app.MainApplication;
import com.application.chiettinhdienluc.data.GlobalData;
import com.application.chiettinhdienluc.model.NhanCongChiTietModel;
import com.application.chiettinhdienluc.utils.Log;

public class NhanCongChiTietDataSource {

	private MySQLiteHelper dbHelper;

	private final String  NHANCONGCHITIET_DB_NAME = "HSO_NCONG_CTIET";

	//table column names
	private static final String BANCTINH_ID = "BANCTINH_ID";
	private static final String MA_CVIEC = "MA_CVIEC";
	private static final String DON_GIA = "DON_GIA";
	private static final String KHOI_LUONG = "KHOI_LUONG";
	private static final String THANH_TIEN = "THANH_TIEN";	
	private static final String HSO_CHIETTINH_ID = "HSO_CHIETTINH_ID";
	private static final String LAN = "LAN";
	private static final String TRANG_THAI = "TRANG_THAI";
	private static final String KHU_VUC = "KHU_VUC";
	private static final String CHITIET_ID = "CHITIET_ID";
	
	private final static String TAG = NhanCongChiTietDataSource.class.getSimpleName();

	public NhanCongChiTietDataSource() {	
		dbHelper = MySQLiteHelper.getInstance();
	}

	public long updateValue(NhanCongChiTietModel nhancongchitiet) {
		long row_id = -1; 
		
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				ContentValues values = new ContentValues();

				values.put(HSO_CHIETTINH_ID, 	GlobalData.globalChietTinh.get_Id());
				values.put(MA_CVIEC, 			nhancongchitiet.getMaCongViec());
				values.put(DON_GIA, 			nhancongchitiet.getDongia());
				values.put(KHOI_LUONG, 			nhancongchitiet.getKhoiLuong());
				values.put(THANH_TIEN, 			nhancongchitiet.getDongia() * nhancongchitiet.getKhoiLuong());
				values.put(LAN, 				nhancongchitiet.getLan());
				values.put(TRANG_THAI, 			nhancongchitiet.getTrangthai());
				values.put(KHU_VUC, 			nhancongchitiet.getKhuVuc());
				values.put(BANCTINH_ID, 		nhancongchitiet.getBangChietTinhId());
				values.put(CHITIET_ID, 			0);
				
				if(GlobalData.globalChietTinh.get_Id() <= 0)
					row_id = dbHelper.getDb().insert(NHANCONGCHITIET_DB_NAME, null, values);
				else
				{			
					try {
						deleteItemWithQuantityZero(nhancongchitiet, false);
						row_id = dbHelper.getDb().insert(NHANCONGCHITIET_DB_NAME, null, values);
					} catch (Exception e) {
						row_id = -1;
					}
					finally{				
					}
				}
			}	
		} catch (Exception e) {
		}
		finally{
		}

		return row_id;	
	}

	public int deleteItemWithQuantityZero(NhanCongChiTietModel nhancongchitiet, boolean needToClose) {
		int numRowEffect = 0;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{	
				String whereClause = "HSO_CHIETTINH_ID " + " = ? " + " and " + "KHU_VUC" + " = ?" + " and " + "MA_CVIEC" + " = ?";
				String[] whereArgs = new String[] { ""+GlobalData.globalChietTinh.get_Id(), MainApplication.getMaKhuVuc(), nhancongchitiet.getMaCongViec()};

				numRowEffect = dbHelper.getDb().delete(NHANCONGCHITIET_DB_NAME, whereClause, whereArgs);
			}
		} catch (Exception e) {
		}
		finally{
		}
		return numRowEffect;
	}
	
	public int deleteItemByHoSoChietTinhId(String hosoChietTinhId) {
		int numRowEffect = 0;
		try {
			if(dbHelper != null && dbHelper.getDb() != null)
			{
				String whereClause = "HSO_CHIETTINH_ID " + " = ? " + " and " + "KHU_VUC" + " = ? ";
				String[] whereArgs = new String[] { ""+GlobalData.globalChietTinh.get_Id(), MainApplication.getMaKhuVuc()};

				numRowEffect = dbHelper.getDb().delete(NHANCONGCHITIET_DB_NAME, whereClause, whereArgs);
			}	
		} catch (Exception e) {
		}
		finally{
		}
		return numRowEffect;
	}
	
	public ArrayList<NhanCongChiTietModel> getSelectedNhanCongByHoSoId(long hoSoId)
	{
		Cursor cursor = null;
		ArrayList<NhanCongChiTietModel> nhancongChitiet = new ArrayList<NhanCongChiTietModel>();
		
		try {
			if(dbHelper != null && dbHelper.getDb() != null)	   
			{
				cursor = dbHelper.getDb().rawQuery("select ncct.rowid, ncct.MA_CVIEC, nc.TEN_CVIEC, nc.QUY_CACH, ncct.DON_GIA, ncct.KHOI_LUONG, ncct.THANH_TIEN " +
								"from HSO_NCONG_CTIET ncct inner join D_NHANCONG  nc on ncct.MA_CVIEC = nc.MA_CVIEC  " +
								"where ncct.HSO_CHIETTINH_ID = " + hoSoId , null); 
		        int order = 0;
				if (cursor != null && cursor.getCount() > 0) {
					cursor.moveToFirst();
					
					do {
						NhanCongChiTietModel ct = new NhanCongChiTietModel();
						ct.setNhancongChiTietId(cursor.getInt(0));
						ct.setMaCongViec(cursor.getString(1));
						ct.setTenCongviec(cursor.getString(2));
						ct.setQuycach(cursor.getString(3));
						ct.setDongia(cursor.getDouble(4));		        	 
						ct.setKhoiLuong(cursor.getDouble(5));
						ct.setThanhTien(cursor.getDouble(6));	     
						ct.setKhuVuc(MainApplication.getMaKhuVuc());
		                ct.setCheck(true);
		                ct.setOrder(order);
		                //order++;
						nhancongChitiet.add(ct);

					} while (cursor.moveToNext());
				}
			}	
		} catch (Exception e) {
		}
		finally{
			if (cursor != null && !cursor.isClosed())
			{
				cursor.close();
				cursor = null;
			}
		}

		return nhancongChitiet;
	}

	public ArrayList<NhanCongChiTietModel> getNhancongChiTietByHoSoId(long hoSoId)
	{
		Cursor cursor = null;
		Cursor cursor1 = null;
		ArrayList<NhanCongChiTietModel> nhancongChitiet = new ArrayList<NhanCongChiTietModel>();
		
		try {
			if(dbHelper != null && dbHelper.getDb() != null)		   
			{
				cursor = dbHelper.getDb().
						rawQuery("select ncct.rowid, ncct.MA_CVIEC, nc.TEN_CVIEC, nc.QUY_CACH, ncct.DON_GIA, ncct.KHOI_LUONG, ncct.THANH_TIEN " +
								"from HSO_NCONG_CTIET ncct inner join D_NHANCONG  nc on ncct.MA_CVIEC = nc.MA_CVIEC  " +
								"where ncct.HSO_CHIETTINH_ID = " + hoSoId +
								" and nc.KHU_VUC = '" + MainApplication.getMaKhuVuc() + "'", null); 
		        int order = 0;
				if (cursor != null && cursor.getCount() > 0 ) {
					cursor.moveToFirst();
					do {
						
						NhanCongChiTietModel ct = new NhanCongChiTietModel();
						ct.setNhancongChiTietId(cursor.getInt(0));
						ct.setMaCongViec(cursor.getString(1));
						ct.setTenCongviec(cursor.getString(2));
						ct.setQuycach(cursor.getString(3));
						ct.setDongia(cursor.getDouble(4));		        	 
						ct.setKhoiLuong(cursor.getDouble(5));
						ct.setThanhTien(cursor.getDouble(6));	     
						ct.setKhuVuc(MainApplication.getMaKhuVuc());
		                ct.setCheck(true);
		                ct.setOrder(order);
		                //order++;
						nhancongChitiet.add(ct);

					} while (cursor.moveToNext());
				}

				ArrayList<NhanCongChiTietModel> nhancongChitiet1 = new ArrayList<NhanCongChiTietModel>();

				cursor1 = dbHelper.getDb().
						rawQuery("select rowid, MA_CVIEC, TEN_CVIEC, QUY_CACH, DON_GIA, 0 as KHOI_LUONG, 0 as THANH_TIEN " +
								"from D_NHANCONG " +
								"where SU_DUNG =1 " +
								"and MA_CVIEC not in (select MA_CVIEC " +
														"from HSO_NCONG_CTIET " +
														"where HSO_CHIETTINH_ID = " + hoSoId + 
														" and KHU_VUC = '" + MainApplication.getMaKhuVuc() + 
														"')", null); 
				order = 1;
				if (cursor1 != null && cursor1.getCount() > 0 ) {
					cursor1.moveToFirst();
					do {

						NhanCongChiTietModel ct = new NhanCongChiTietModel();
						ct.setNhancongChiTietId(cursor1.getInt(0));
						ct.setMaCongViec(cursor1.getString(1));
						ct.setTenCongviec(cursor1.getString(2));
						ct.setQuycach(cursor1.getString(3));
						ct.setDongia(cursor1.getDouble(4));
						ct.setKhoiLuong(cursor1.getDouble(5));
						ct.setThanhTien(cursor1.getDouble(6));
						ct.setBangChietTinhId("");
						ct.setKhuVuc(MainApplication.getMaKhuVuc());
						ct.setCheck(false);
						ct.setOrder(order);
			            //order++;
						nhancongChitiet1.add(ct);

					} while (cursor1.moveToNext());
				}
				
				nhancongChitiet.addAll(nhancongChitiet1);		

				Log.i("Nhan cong Count", " "  + nhancongChitiet.size());
			}	
		} catch (Exception e) {
		}
		finally{
			if (cursor != null && !cursor.isClosed()) {
				cursor.close();
				cursor = null;
			}
			
			if (cursor1 != null && !cursor1.isClosed()) {
				cursor1.close();
				cursor1 = null;
			}
		}

		return nhancongChitiet;
	}

}